package com.vector.ding.web.service.baseinfo;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONObject;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.PageHelper;
import com.vector.ding.commons.ActionResult;
import com.vector.ding.commons.BizConstant;
import com.vector.ding.commons.Constant;
import com.vector.ding.commons.util.BeanUtil;
import com.vector.ding.commons.util.StringUtil;
import com.vector.ding.commons.util.collection.CollectionUtil;
import com.vector.ding.commons.util.file.ExcelUtil;
import com.vector.ding.commons.util.web.RequestUtil;
import com.vector.ding.dao.mybatis.mapper.XdgsBaseInfoMapper;
import com.vector.ding.dao.mybatis.mapper.XdgsBizInfo1Mapper;
import com.vector.ding.dao.mybatis.model.XdgsBaseInfo;
import com.vector.ding.dao.mybatis.model.XdgsBaseInfoExample;
import com.vector.ding.dao.mybatis.model.XdgsBizInfo1;
import com.vector.ding.dao.mybatis.model.XdgsBizInfo1Example;
import com.vector.ding.exception.BaseException;

@Service
public class XdgsBaseInfoMgrService {
	
	@Autowired
	private XdgsBaseInfoMapper baseMapper;
	@Autowired
	private XdgsBizInfo1Mapper biz1Mapper;
	
	private static Logger logger = LoggerFactory.getLogger(GqtzgsBaseInfoMgrService.class);

	public XdgsBaseInfo getBaseInfo(String number) {
		return baseMapper.selectByPrimaryKey(number);
	}
	
	public XdgsBaseInfo getBaseInfoByName(String companyName) {
		XdgsBaseInfoExample example = new XdgsBaseInfoExample();
		example.or().andCompanyNameEqualTo(companyName);
		List<XdgsBaseInfo> list = baseMapper.selectByExample(example);
		return CollectionUtil.isEmpty(list) ? null : list.get(0);
	}

	public List<XdgsBaseInfo> getBaseInfo(int pageIndex, int pageSize,
			HttpServletRequest request) {
		if (pageSize != Integer.MAX_VALUE)
			PageHelper.startPage(pageIndex, pageSize, true);
		XdgsBaseInfoExample example = getBaseInfoExample(request);
		return baseMapper.selectByExample(example);
	}

	private XdgsBaseInfoExample getBaseInfoExample(HttpServletRequest request) {
		XdgsBaseInfoExample example = new XdgsBaseInfoExample();
		String companyName = StringUtil.getString(request.getParameter("company_name")).trim();
		example.or().andCompanyNameLike(StringUtil.wrapString(companyName, "%", "%"));
		return example;
	}

	public XdgsBaseInfo getBaseInfoBean(HttpServletRequest request) {
		Map<String, Object> paramMap = RequestUtil.getParamMap(request);
		Set<String> excludeProSet = CollectionUtil.asSet("registerFund");
		XdgsBaseInfo baseInfo = new XdgsBaseInfo();
		BeanUtil.fillBeanProperty(paramMap, baseInfo, excludeProSet);
		baseInfo.setRegisterFund(ExcelUtil.getDecimalValue(
				StringUtil.getString(paramMap.get("registerFund"))));
		if (StringUtil.isEmpty(baseInfo.getNumber()))
			baseInfo.setNumber(StringUtil.getUuid());

		return baseInfo;
	}

	public JSONObject insertBaseInfo(XdgsBaseInfo baseInfo) {
		if (existRecord(baseInfo.getNumber()))
			return ActionResult.fail(String.format("新增失败：存在编号为[%s]的记录!", baseInfo.getNumber()));
		
		int cnt = 0;
		try {
			cnt = baseMapper.insertSelective(baseInfo);
		} catch (Exception e) {
			if (logger.isDebugEnabled())
				e.printStackTrace();
		}
		if (cnt == 0) {
			logger.error("插入小贷投资公司基本信息记录失败：number为{}...", baseInfo.getNumber());
		}
			
		return cnt != 0 ? ActionResult.success() : 
			ActionResult.fail(String.format("新增记录失败"));
	}
	
	public JSONObject updateBaseInfo(XdgsBaseInfo baseInfo) {
		if (!existRecord(baseInfo.getNumber()))
			return ActionResult.fail(String.format("更新失败：不存在编号为[%s]的记录!", baseInfo.getNumber()));
		int cnt = 0;
		try {
			cnt = baseMapper.updateByPrimaryKeySelective(baseInfo);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (cnt == 0) {
			logger.error("更新小贷投资公司基本信息记录失败：未找到number为{}的记录...", baseInfo.getNumber());
		}
			
		return cnt != 0 ? ActionResult.success() : 
			ActionResult.fail(String.format("更新基本信息记录失败，编号为[%s]", baseInfo.getNumber()));
	}
	
	public boolean existRecord(String number) {
		XdgsBaseInfoExample example = new XdgsBaseInfoExample();
		example.or().andNumberEqualTo(number);
		return baseMapper.countByExample(example) != 0;
	}

	public JSONObject delete(String[] ids) {
		int count = Constant.DaoOperation.UPDATE_FAILURE ;
		if (ids == null || ids.length == 0)
			count = 0;
		
		try {
			for (String id: ids) {
				// 删除业务1表中同名公司的记录数据
				XdgsBaseInfo baseInfo = getBaseInfo(id);
				if (baseInfo == null)
					continue;
				String companyName = baseInfo.getCompanyName();
				biz1Mapper.deleteByPrimaryKey(companyName);
				
				count += baseMapper.deleteByPrimaryKey(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if (count == Constant.DaoOperation.UPDATE_FAILURE)
			return ActionResult.fail("删除失败！");
		return ActionResult.success();
	}

	public void importFormFile(File importFile) {
		HSSFWorkbook workBook = null;
		int rowIndex = 0;
		try {
			workBook = new HSSFWorkbook(FileUtils.openInputStream(importFile));
			HSSFSheet sheet = workBook.getSheetAt(0);
			
			int rowNum = sheet.getLastRowNum();
			for (rowIndex = BizConstant.Xedkgs.Base.START_ROW_NUM; rowIndex <= rowNum; ++rowIndex) {
				HSSFRow row = sheet.getRow(rowIndex);
				if (isEnd(row))
					break;

				String companyName = row.getCell(BizConstant.Xedkgs.COMPANY_NAME_CELL).toString();
				if (StringUtil.isEmpty(companyName))
					break;
				if (!exiestBaseRecord(companyName)) {
					insertBaseInfo(companyName);
				}
				
				XdgsBizInfo1 bean = getBaseInfoBean(row);
				if (!exiestBiz1Record(companyName)) {
					biz1Mapper.insertSelective(bean);
				} else {
					XdgsBizInfo1Example example = new XdgsBizInfo1Example();
					example.or().andNumberEqualTo(companyName);
					biz1Mapper.updateByExampleSelective(bean, example);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
//			throw new BaseException(e);
			throw new BaseException(String.format("第%s行数据格式有误，请检查数据！", rowIndex), e);
		} finally {
			if (workBook != null) {
				try {
					workBook.close();
				} catch (IOException e) {
					e.printStackTrace();
					throw new BaseException(e);
				}
			}
		}
	}
	
	private void insertBaseInfo(String companyName) {
		XdgsBaseInfo baseInfo = new XdgsBaseInfo();
		baseInfo.setCompanyName(companyName);
		baseInfo.setNumber(UUID.randomUUID().toString());
		baseMapper.insertSelective(baseInfo);
	}

	private XdgsBizInfo1 getBaseInfoBean(HSSFRow row) {
		XdgsBizInfo1 bean = new XdgsBizInfo1();
		bean.setAverageDeadline((int)row.getCell(BizConstant.Xedkgs.Biz1.AVERAGE_DEADLINE_CELL).getNumericCellValue());
		bean.setAverageYearRate(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.AVERAGE_TEAR_RATE_CELL)));
		bean.setBankFinancingBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.BANK_FINANCING_BALANCE_CELL)));
		bean.setBankFinancingDbRemark(StringUtil.getString(row.getCell(BizConstant.Xedkgs.Biz1.BANK_FINANCING_DB_REMARK_CELL)));
		bean.setBankFinancingDbType(StringUtil.getString(row.getCell(BizConstant.Xedkgs.Biz1.BANK_FINANCING_DB_TYPE_CELL)));
		bean.setBankFinancingRemark(StringUtil.getString(row.getCell(BizConstant.Xedkgs.Biz1.BANK_FINANCING_REMARK_CELL)));
		bean.setBankName1(StringUtil.getString(row.getCell(BizConstant.Xedkgs.Biz1.BANK_NAME_1_CELL)));
		bean.setBankName2(StringUtil.getString(row.getCell(BizConstant.Xedkgs.Biz1.BANK_NAME_2_CELL)));
		bean.setBzLoan(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.BZ_LOAN_CELL)));
		bean.setCompanyName(StringUtil.getString(row.getCell(BizConstant.Xedkgs.COMPANY_NAME_CELL)));
		bean.setCreditBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.CREDIT_BALANCE_CELL)));
		bean.setCreditBalanceAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.CREDIT_BALANCE_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setCreditBalanceTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.CREDIT_BALANCE_TIMES_CELL).getNumericCellValue());
		bean.setCurTotalProfitAfterTax(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.CUR_TOATL_PROFIT_AFTER_TAX_CELL)));
		bean.setCurTotalProfitPreTax(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.CUR_TOATL_PROFIT_PRE_TAX_CELL)));
		bean.setCyqyLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.CYQY_LOAN_BALANCE_CELL)));
		bean.setCyqyLoanBalanceTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.CYQY_LOAN_BALANCE_TIMES_CELL).getNumericCellValue());
		bean.setCyqyTotalAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.CYQY_TOTAL_LOAN_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setCyqyTotalLoanMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.CYQY_TOTAL_LOAN_MONEY_CELL)));
		bean.setCyqyTotalLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.CYQY_TOTAL_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setDyLoan(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.DY_LOAN_CELL)));
		bean.setFinancingMoney1(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.FINANCING_MONEY_1_CELL)));
		bean.setFinancingMoney2(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.FINANCING_MONEY_2_CELL)));
		bean.setFsngrLoanAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.FSNGR_LOAN_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setFsngrLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.FSNGR_LOAN_BALANCE_CELL)));
		bean.setFsngrLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.FSNGR_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setFsnxqyLoanAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.FSNXQE_LOAN_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setFsnxqyLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.FSNXQE_LOAN_BALANCE_CELL)));
		bean.setFsnxqyLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.FSNXQE_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setHighestYearRate(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.HIGHEST_TEAR_RATE_CELL)));
		bean.setKjqyLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.KJQY_LOAN_BALANCE_CELL)));
		bean.setKjqyLoanBalanceTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.KJQY_LOAN_BALANCE_TIMES_CELL).getNumericCellValue());
		bean.setKjqyTotalAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.KJQY_TOTAL_LOAN_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setKjqyTotalLoanMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.KJQY_TOTAL_LOAN_MONEY_CELL)));
		bean.setKjqyTotalLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.KJQY_TOTAL_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setLoanFdcIndustry(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_FDC_INDUSTRY_CELL)));
		bean.setLoanFirstQuarter(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_FIRST_QUARTER_CELL)));
		bean.setLoanForthQuarter(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_FORTH_QUARTER_CELL)));
		bean.setLoanJzIndustry(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_JZ_INDUSTRY_CELL)));
		bean.setLoanLtIndustry(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_LT_INDUSTRY_CELL)));
		bean.setLoanNlmfyIndustry(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_NLMFY_INDUSTRY_CELL)));
		bean.setLoanOtherIndustry(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_OTHER_INDUSTRY_CELL)));
		bean.setLoanRemark(StringUtil.getString(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_REMARK_CELL)));
		bean.setLoanSecondQuarter(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_SECOND_QUARTER_CELL)));
		bean.setLoanThirdQuarter(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOAN_THIRD_QUARTER_CELL)));
		bean.setLowestYearRate(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.LOWEST_TEAR_RATE_CELL)));
		bean.setNetAsset(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.NET_ASSET_CELL)));
		bean.setNumber(UUID.randomUUID().toString());
		bean.setOverdueLoanMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.OVERDUE_LOAN_MONEY_CELL)));
		bean.setOverdueLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.OVERDUE_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setReferBizTotalMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.REFER_BIZ_TOTAL_MONEY_CELL)));
		bean.setReferBizTotalTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.REFER_BIZ_TOTAL_TIMES_CELL).getNumericCellValue());
		//bean.setRiskProvisionMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.PI)));
		bean.setSannongLoanAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.SANNONG_LOAN_ACCOUNT_CELL).getNumericCellValue());
		bean.setSannongLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.SANNONG_LOAN_BALANCE_CELL)));
		bean.setSannongLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.SANNONG_LOAN_BALANCE_CELL).getNumericCellValue());
		bean.setSingleLoanBelow50wNum((int)row.getCell(BizConstant.Xedkgs.Biz1.SINGLE_LOAN_BELOW50W_NUM_CELL).getNumericCellValue());
		bean.setSndkAverageYearRate(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.SNDK_AVERAGE_TEAR_RATE_CELL)));
		bean.setTotalAsset(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.TOTAL_ASSET_CELL)));
		bean.setTotalCreditAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.TOTAL_CREDIT_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setTotalCreditMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.TOTAL_CREDIT_MONEY_CELL)));
		bean.setTotalCreditTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.TOTAL_CREDIT_TIMES_CELL).getNumericCellValue());
		bean.setWhcylqyLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.WHCYLQYQY_LOAN_BALANCE_CELL)));
		bean.setWhcylqyLoanBalanceTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.WHCYLQYQY_LOAN_BALANCE_TIMES_CELL).getNumericCellValue());
		bean.setWhcylqyTotalLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.WHCYLQYQY_TOTAL_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setWhcylqyTotalLoanMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.WHCYLQYQY_TOTAL_LOAN_MONEY_CELL)));
		bean.setWxqyLoanBalance(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.WXQY_LOAN_BALANCE_CELL)));
		bean.setWxqyLoanBalanceTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.WXQY_LOAN_BALANCE_TIMES_CELL).getNumericCellValue());
		bean.setWxqyTotalAccountNum((int)row.getCell(BizConstant.Xedkgs.Biz1.WXQY_TOTAL_LOAN_ACCOUNT_NUM_CELL).getNumericCellValue());
		bean.setWxqyTotalLoanMoney(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.WXQY_TOTAL_LOAN_MONEY_CELL)));
		bean.setWxqyTotalLoanTimes((int)row.getCell(BizConstant.Xedkgs.Biz1.WXQY_TOTAL_LOAN_TIMES_CELL).getNumericCellValue());
		bean.setXyLoan(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.XY_LOAN_CELL)));
		bean.setZyLoan(ExcelUtil.getCellDecimalValue(row.getCell(BizConstant.Xedkgs.Biz1.ZY_LOAN_CELL)));
		
		return bean;
	}
	
	private boolean isEnd(HSSFRow row) {
		String stringValue = StringUtil.getString(row.getCell(BizConstant.Xedkgs.Base.NUMBER_CELL));
		if (StringUtil.isEmpty(stringValue))
			return true;
		
		return false;
	}
	
	private boolean exiestBiz1Record(String companyName) {
		XdgsBizInfo1Example example = new XdgsBizInfo1Example();
		example.or().andCompanyNameEqualTo(companyName);
		return biz1Mapper.countByExample(example) != 0;
	}
	
	private boolean exiestBaseRecord(String companyName) {
		XdgsBaseInfoExample example = new XdgsBaseInfoExample();
		example.or().andCompanyNameEqualTo(companyName);
		return baseMapper.countByExample(example) != 0;
	}
	
	public boolean exiestCompany(String companyName) {
		XdgsBaseInfoExample example = new XdgsBaseInfoExample();
		example.or().andCompanyNameEqualTo(companyName);
		return baseMapper.countByExample(example) != 0;
	}

/*	private boolean checkRowData(HSSFRow row) {
		String number = StringUtil.getString((int)row.getCell(BizConstant.Gqtzgs.Base.NUMBER_CELL).getNumericCellValue());
		if (StringUtil.isEmpty(number))
			return false;
		
		return !existRecord(number);
	}*/

}
